package com.example.coursedesign.ui.dashboard.dao;

import android.app.Application;
import android.content.Context;
import android.os.Handler;
import android.os.HandlerThread;
import android.os.Looper;
import android.util.Log;
import android.widget.ImageView;

import com.example.coursedesign.ui.dashboard.entity.Science;
import com.example.coursedesign.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ScienceLikeDao {
    private static final String TAG = "mysql-coursedesign-ScienceLikeDao";
    private final Handler handler;
    private final HandlerThread handlerThread;

    public ScienceLikeDao(Context context) {
        handlerThread = new HandlerThread("DatabaseOperations");
        handlerThread.start();
        handler = new Handler(handlerThread.getLooper());
    }

    // 获取用户收藏的所有科普文章信息
    public void getSciencesByAccountId(final String account, final OnSciencesFetchedListener listener) {
        handler.post(() -> {
            List<Science> sciences = new ArrayList<>();
            try (Connection connection = JDBCUtils.getConn()) {
                if (connection != null) {
                    String sql = "SELECT s.id, s.title, s.image, s.category, s.publish_time, s.read_count, s.content " +
                            "FROM science s " +
                            "JOIN sciencelike sl ON s.id = sl.science_id " +
                            "WHERE sl.account = ?";
                    try (PreparedStatement ps = connection.prepareStatement(sql)) {
                        ps.setString(1, account);
                        try (ResultSet rs = ps.executeQuery()) {
                            while (rs.next()) {
                                int id = rs.getInt("id");
                                String title = rs.getString("title");
                                String category = rs.getString("category");
                                String publish_time = rs.getString("publish_time");
                                int read_count = rs.getInt("read_count"); // 从 ResultSet 中读取 read_count
                                String content = rs.getString("content");
                                String image = rs.getString("image");

                                // 按照 Science 类的构造函数顺序传递参数
                                sciences.add(new Science(id, title, category, publish_time, read_count, content, image));
                            }
                            // 回到主线程更新UI
                            new Handler(Looper.getMainLooper()).post(() -> listener.onSciencesFetched(sciences));
                        }
                    }
                } else {
                    Log.e(TAG, "无法获取数据库连接");
                    listener.onError("无法获取数据库连接");
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Log.e(TAG, "异常getSciencesByAccountId: " + e.getMessage());
                listener.onError(e.getMessage());
            }
        });
    }
    // 检查用户是否已收藏该帖子
    public void isFavorited(final int scienceId, final String account, final OnFavoritedListener listener) {
        handler.post(() -> {
            try (Connection connection = JDBCUtils.getConn()) {
                if (connection != null) {
                    String sql = "SELECT * FROM scienceLike WHERE science_id = ? AND account = ?";
                    try (PreparedStatement ps = connection.prepareStatement(sql)) {
                        ps.setInt(1, scienceId);
                        ps.setString(2, account);  // 使用传入的账户参数
                        try (ResultSet rs = ps.executeQuery()) {
                            final boolean isFavorited = rs.next();
                            // 回到主线程更新UI
                            new Handler(Looper.getMainLooper()).post(() -> listener.onFavoritedResult(isFavorited));
                        }
                    }
                } else {
                    Log.e(TAG, "无法获取数据库连接");
                    listener.onError("无法获取数据库连接");
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Log.e(TAG, "异常isFavorited: " + e.getMessage());
                listener.onError(e.getMessage());
            }
        });
    }

    // 添加收藏记录
    public void addFavorite(final int scienceId, final String account, final OnOperationCompleteListener listener) {
        handler.post(() -> {
            try (Connection connection = JDBCUtils.getConn()) {
                if (connection != null) {
                    // 先从 Science 表获取 category, title, image
                    String querySql = "SELECT category, title, image FROM science WHERE id = ?";
                    try (PreparedStatement queryPs = connection.prepareStatement(querySql)) {
                        queryPs.setInt(1, scienceId);
                        try (ResultSet rs = queryPs.executeQuery()) {
                            if (rs.next()) {
                                String category = rs.getString("category");
                                String title = rs.getString("title");
                                String image = rs.getString("image");

                                // 使用正确的字段名 'science_id' 并包括 category, title, image
                                String insertSql = "INSERT INTO scienceLike (science_id, account, category, title, image) VALUES (?, ?, ?, ?, ?)";
                                try (PreparedStatement ps = connection.prepareStatement(insertSql)) {
                                    ps.setInt(1, scienceId);
                                    ps.setString(2, account);
                                    ps.setString(3, category);
                                    ps.setString(4, title);
                                    ps.setString(5, image);
                                    int rowsAffected = ps.executeUpdate();

                                    // 检查是否成功插入了记录
                                    boolean success = rowsAffected > 0;
                                    // 回到主线程更新UI
                                    new Handler(Looper.getMainLooper()).post(() -> listener.onOperationComplete(success));
                                }
                            } else {
                                // 如果没有找到对应的文章，则返回错误信息
                                Log.e(TAG, "无法找到ID为 " + scienceId + " 的科普文章");
                                listener.onError("无法找到该科普文章");
                            }
                        }
                    }
                } else {
                    Log.e(TAG, "无法获取数据库连接");
                    listener.onError("无法获取数据库连接");
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Log.e(TAG, "异常addFavorite: " + e.getMessage());
                listener.onError(e.getMessage());
            }
        });
    }

    // 删除收藏记录
    public void removeFavorite(final int scienceId, final String account, final OnOperationCompleteListener listener) {
        handler.post(() -> {
            try (Connection connection = JDBCUtils.getConn()) {
                if (connection != null) {
                    String sql = "DELETE FROM scienceLike WHERE science_id = ? AND account = ?";
                    try (PreparedStatement ps = connection.prepareStatement(sql)) {
                        ps.setInt(1, scienceId);
                        ps.setString(2, account);  // 使用传入的账户参数
                        ps.executeUpdate();
                        // 回到主线程更新UI
                        new Handler(Looper.getMainLooper()).post(() -> listener.onOperationComplete(true));
                    }
                } else {
                    Log.e(TAG, "无法获取数据库连接");
                    listener.onError("无法获取数据库连接");
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Log.e(TAG, "异常removeFavorite: " + e.getMessage());
                listener.onError(e.getMessage());
            }
        });
    }
    /**
     * 查询用户收藏的所有科普文章的基本信息
     */
    public void getFavoriteSciences(final String account, final OnFavoriteSciencesListener listener) {
        handler.post(() -> {
            List<Science> favoriteSciences = new ArrayList<>();
            try (Connection connection = JDBCUtils.getConn()) {
                if (connection != null) {
                    String sql = "SELECT s.id, s.title, s.category, s.publish_time, s.read_count, s.content, s.image " +
                            "FROM science s " +
                            "JOIN scienceLike sl ON s.id = sl.science_id " +
                            "WHERE sl.account = ?";
                    try (PreparedStatement ps = connection.prepareStatement(sql)) {
                        ps.setString(1, account);
                        try (ResultSet rs = ps.executeQuery()) {
                            while (rs.next()) {
                                Science science = new Science(
                                        rs.getInt("id"),
                                        rs.getString("title"),
                                        rs.getString("category"),
                                        rs.getString("publish_time"),
                                        rs.getInt("read_count"),
                                        rs.getString("content"),
                                        rs.getString("image")
                                );
                                favoriteSciences.add(science);
                            }
                            // 回到主线程更新UI
                            new Handler(Looper.getMainLooper()).post(() -> listener.onFavoriteSciencesReceived(favoriteSciences));
                        }
                    }
                } else {
                    Log.e(TAG, "无法获取数据库连接");
                    listener.onError("无法获取数据库连接");
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Log.e(TAG, "异常getFavoriteSciences: " + e.getMessage());
                listener.onError(e.getMessage());
            }
        });
    }

    // 新增回调接口
    public interface OnFavoriteSciencesListener {
        void onFavoriteSciencesReceived(List<Science> sciences);
        void onError(String errorMessage);
    }

    // 回调接口
    public interface OnFavoriteCountListener {
        void onFavoriteCountReceived(int count);
        void onError(String errorMessage);
    }

    public interface OnFavoritedListener {
        void onFavoritedResult(boolean isFavorited);
        void onError(String errorMessage);
    }

    public interface OnOperationCompleteListener {
        void onOperationComplete(boolean success);
        void onError(String errorMessage);
    }

    public interface OnSciencesFetchedListener {
        void onSciencesFetched(List<Science> sciences);
        void onError(String errorMessage);
    }
}